package de.hach.manno;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;
import de.hach.manno.webservice.Club;
import de.hach.manno.webservice.Event;
import de.hach.manno.webservice.Match;
import de.hach.manno.webservice.Player;
import de.hach.manno.webservice.PlaysFor;

/**
 * Common container for database related functionality
 * 
 * @author hanspeter
 * @version 1.0
 * @since 16/06/2012
 * 
 */
public class MannOData {
	private static final String TAG = "MannOData";

	private final SimpleDateFormat sdf = new SimpleDateFormat(
			"yyyy-MM-dd HH:mm:ss");

	// Basic Database Information
	static final int VERSION = 1;
	static final String DATABASE = "manno.db";

	// Table club
	static final String TABLE_CLUB = "club";
	static final String C_ID = BaseColumns._ID;
	static final String C_NAME = "name";
	static final String C_TIMESTAMP = "timestamp";
	// Table event
	static final String TABLE_EVENT = "event";
	static final String E_ID = BaseColumns._ID;
	static final String E_MATCHID = "matchID";
	static final String E_TYPE = "type";
	static final String E_PLAYSFORID = "playsForID";
	static final String E_PLAYTIME = "playtime";
	static final String E_PREVIOUSEVENTID = "previousEventID";
	static final String E_DESCRIPTION = "description";
	static final String E_TIMESTAMP = "timestamp";
	// Table match
	static final String TABLE_MATCH = "match";
	static final String M_ID = BaseColumns._ID;
	static final String M_HOMECLUBID = "homeClubID";
	static final String M_AWAYCLUBID = "awayClubID";
	static final String M_DATE = "date";
	static final String M_TIMESTAMP = "timestamp";
	// Table player
	static final String TABLE_PLAYER = "player";
	static final String P_ID = BaseColumns._ID;
	static final String P_NAME = "name";
	static final String P_PICTURE = "picture";
	static final String P_TIMESTAMP = "timestamp";
	// Table playsFor
	static final String TABLE_PLAYSFOR = "playsFor";
	static final String PF_ID = BaseColumns._ID;
	static final String PF_PLAYERID = "playerID";
	static final String PF_CLUBID = "clubID";
	static final String PF_START = "start";
	static final String PF_END = "end";
	static final String PF_TIMESTAMP = "timestamp";

	// View for eventList
	static final String VIEW_EVENTLIST = "eventList";
	static final String EL_MATCH = "match";

	// View for latest update
	static final String VIEW_UPDATE = "updatetimestamp";
	static final String U_LATESTUPDATE = "latestUpdate";

	/**
	 * Internal class implementation of DbHelper
	 */
	class DbHelper extends SQLiteOpenHelper {

		public DbHelper(Context context) {
			super(context, DATABASE, null, VERSION);

		}

		/**
		 * timestamp is of type TEXT -> enter datetime('now') : YYYY-MM-DD
		 * HH:mm:ss
		 */
		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.i(TAG, "Creating database: " + DATABASE);

			// Create table club
			String sql = "Create table '" + TABLE_CLUB + "' (" + C_ID
					+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + C_NAME
					+ " TEXT, " + C_TIMESTAMP + " TEXT)";
			db.execSQL(sql);
			Log.d(TAG, "on Created " + TABLE_CLUB + ": " + sql);

			// create table event
			sql = "Create table '" + TABLE_EVENT + "' (" + E_ID
					+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + E_MATCHID
					+ " INTEGER, " + E_TYPE + " TEXT, " + E_PLAYSFORID
					+ " INTEGER, " + E_PLAYTIME + " INTEGER, "
					+ E_PREVIOUSEVENTID + " INTEGER, " + E_DESCRIPTION
					+ " TEXT, " + C_TIMESTAMP + " TEXT)";
			db.execSQL(sql);
			Log.d(TAG, "on Created " + TABLE_EVENT + ": " + sql);

			// create table match
			sql = "Create table '" + TABLE_MATCH + "' (" + M_ID
					+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + M_HOMECLUBID
					+ " INTEGER, " + M_AWAYCLUBID + " INTEGER, " + M_DATE
					+ " TEXT, " + C_TIMESTAMP + " TEXT)";
			db.execSQL(sql);
			Log.d(TAG, "on Created " + TABLE_MATCH + ": " + sql);

			// create table player
			sql = "CREATE TABLE " + TABLE_PLAYER + " ( " + P_ID
					+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + P_NAME
					+ " TEXT, " + P_PICTURE + " BLOB, " + P_TIMESTAMP
					+ " TEXT )";
			db.execSQL(sql);
			Log.d(TAG, "on Created " + TABLE_PLAYER + ": " + sql);

			// create table playsFor
			sql = "CREATE TABLE " + TABLE_PLAYSFOR + " ( " + PF_ID
					+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + PF_PLAYERID
					+ " INTEGER, " + PF_CLUBID + " INTEGER, " + PF_START
					+ " TEXT, " + PF_END + " TEXT, " + PF_TIMESTAMP + " TEXT )";
			db.execSQL(sql);
			Log.d(TAG, "on Created " + TABLE_PLAYSFOR + ": " + sql);

			// create view eventList
			sql = "create view " + VIEW_EVENTLIST + " as Select e." + E_ID
					+ ", e." + E_TYPE + ", e." + E_DESCRIPTION + ", e."
					+ E_PLAYTIME + ", c1." + C_NAME + " || ' - ' || c2."
					+ C_NAME + " as " + EL_MATCH + " ,e." + E_TIMESTAMP
					+ " from " + TABLE_EVENT + " e left join  " + TABLE_MATCH
					+ " m on e." + E_MATCHID + " = m." + M_ID + " left join "
					+ TABLE_CLUB + " c1 on m." + M_HOMECLUBID + " = c1." + C_ID
					+ " left join " + TABLE_CLUB + " c2 on m." + M_AWAYCLUBID
					+ "  =c2." + C_ID + "";
			db.execSQL(sql);
			Log.d(TAG, "on Created " + VIEW_EVENTLIST + ": " + sql);

			// create view update
			sql = "create view " + VIEW_UPDATE + " as Select max( max( c."
					+ C_TIMESTAMP + " ), max( e." + E_TIMESTAMP + " ), max( m."
					+ M_TIMESTAMP + "), max( p." + P_TIMESTAMP + " ), max( pf."
					+ PF_TIMESTAMP + ")) as " + U_LATESTUPDATE + " from "
					+ TABLE_CLUB + " c , " + TABLE_EVENT + " e, " + TABLE_MATCH
					+ " m, " + TABLE_PLAYER + " p, " + TABLE_PLAYSFOR + " pf";
			db.execSQL(sql);
			Log.d(TAG, "on Created " + VIEW_UPDATE + ": " + sql);

			// TODO use method to generate Testdata for debugging
			// createTestData(db);

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.i(TAG, "Upgrade database " + DATABASE + " from version "
					+ oldVersion + " to " + newVersion);
			// TODO Use method for altering or updating methods in case of new
			// databse version

		}

		/**
		 * Simple method to generate test data in the database -> for debugging
		 * only
		 */
		public void createTestData(SQLiteDatabase db) {
			Log.d(TAG, "create test data");
			ContentValues values = new ContentValues();
			Calendar cal = Calendar.getInstance();
			try {
				cal.setTime(sdf.parse("1970-01-01 00:00"));
			} catch (ParseException e) {
				Log.d(TAG, "create test data parsing error");
			}
			// Insert clubs
			values.clear();
			values.put(C_NAME, "SG Ölbronn-Dürrn I");
			values.put(C_TIMESTAMP, sdf.format(cal.getTime()));
			db.insert(TABLE_CLUB, null, values);
			values.clear();
			values.put(C_NAME, "SG Ölbronn-Dürrn II");
			values.put(C_TIMESTAMP, sdf.format(cal.getTime()));
			db.insert(TABLE_CLUB, null, values);
			values.clear();
			values.put(C_NAME, "1.FC Bauschlott");
			values.put(C_TIMESTAMP, sdf.format(cal.getTime()));
			db.insert(TABLE_CLUB, null, values);
			values.clear();
			values.put(C_NAME, "1.FC Nussbaum");
			values.put(C_TIMESTAMP, sdf.format(cal.getTime()));
			db.insert(TABLE_CLUB, null, values);
			values.clear();
			// Insert matches
			values.put(M_HOMECLUBID, 1);
			values.put(M_AWAYCLUBID, 3);
			values.put(M_DATE, "2012-09-23 15:00:00");
			values.put(M_TIMESTAMP, sdf.format(cal.getTime()));
			db.insert(TABLE_MATCH, null, values);
			values.put(M_HOMECLUBID, 2);
			values.put(M_AWAYCLUBID, 4);
			values.put(M_DATE, "2012-09-23 15:00:00");
			values.put(M_TIMESTAMP, sdf.format(cal.getTime()));
			db.insert(TABLE_MATCH, null, values);
			// Insert events
			String[] type = { "goal", "yellow", "red" };
			for (int i = 0; i < 100; i++) {
				values.clear();
				values.put(E_MATCHID, i % 2 + 1);
				values.put(E_TYPE, type[(int) (Math.random() * 3)]);
				values.put(E_DESCRIPTION, "TEST Description");
				values.put(E_PLAYTIME, i);
				values.put(E_TIMESTAMP, sdf.format(cal.getTime()));
				db.insert(TABLE_EVENT, null, values);
			}
			// Insert Player
			values.clear();
			values.put(P_NAME, "Hanspeter Höschle");
			values.put(P_TIMESTAMP, sdf.format(cal.getTime()));
			db.insert(TABLE_PLAYER, null, values);
			values.clear();
			values.put(P_NAME, "Christian Hahnke");
			values.put(P_TIMESTAMP, sdf.format(cal.getTime()));
			db.insert(TABLE_PLAYER, null, values);
			// Insert playsFor
			values.clear();
			values.put(PF_CLUBID, 1);
			values.put(PF_PLAYERID, 1);
			values.put(P_TIMESTAMP, sdf.format(cal.getTime()));
			db.insert(TABLE_PLAYSFOR, null, values);
			values.clear();
			values.put(PF_CLUBID, 2);
			values.put(PF_PLAYERID, 2);
			values.put(P_TIMESTAMP, sdf.format(cal.getTime()));
			db.insert(TABLE_PLAYSFOR, null, values);
			Log.d(TAG, "Test data created");

		}

	}

	private final DbHelper dbHelper;

	public MannOData(Context context) {
		this.dbHelper = new DbHelper(context);
		Log.i(TAG, "Initialized data");
	}

	public void close() {
		this.dbHelper.close();
	}

	/**
	 * 
	 * @return Cursor to all events ordered by timestamp
	 */
	public Cursor getEventUpdates() {
		Log.d(TAG, "getEventUpdates");
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		return db.query(VIEW_EVENTLIST, null, null, null, null, null,
				E_TIMESTAMP + " DESC");

	}

	public Cursor getClubNames() {
		Log.d(TAG, "getClubNames");
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		String[] select = {C_ID, C_NAME };
		return db.query(TABLE_CLUB, select, null, null, null, null, C_NAME
				+ " ASC");
	}

	/**
	 * 
	 * @return Calendar object containing timestamp of latest update in database
	 */
	public Calendar getLatestUpdate() {
		Log.d(TAG, "getEventUpdates");
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.query(VIEW_UPDATE, null, null, null, null, null,
				null);
		Log.d(TAG,
				"Cols: " + cursor.getColumnCount() + " Rows: "
						+ cursor.getCount() + " " + cursor.moveToFirst());
		String timeString = cursor.getString(0);
		if (timeString == null) {
			timeString = "1970-01-01 00:00:00";
		}
		Log.d(TAG, "Latest update in local db: " + timeString);
		Calendar cal = Calendar.getInstance();
		try {
			cal.setTime(sdf.parse(timeString));
		} catch (ParseException e) {
			Log.e(TAG, "ParseException: " + e.getMessage());
			return null;
		}
		return cal;
	}

	public void writeClubsToDatabase(List<Club> clubList) {
		Log.d(TAG, "writeClubsToDatabase");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		for (Club club : clubList) {
			Log.d(TAG, "Write " + club.toString() + " to database");
			values.clear();
			values.put(C_ID, club.getId());
			values.put(C_NAME, club.getName());
			values.put(C_TIMESTAMP, club.getTimestamp());
			db.replace(TABLE_CLUB, null, values);
		}
		// TODO send update Notification
	}

	public void writeEventsToDatabase(List<Event> eventList) {
		Log.d(TAG, "writeEventsToDatabase");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		for (Event event : eventList) {
			Log.d(TAG, "Write " + event.toString() + " to database");
			values.clear();
			values.put(E_ID, event.getId());
			values.put(E_MATCHID, event.getMatchId());
			values.put(E_PLAYSFORID, event.getPlaysForId());
			values.put(E_PLAYTIME, event.getPlaytime());
			values.put(E_PREVIOUSEVENTID, event.getPreviousEventId());
			values.put(E_TYPE, event.getType());
			values.put(E_TIMESTAMP, event.getTimestamp());
			db.replace(TABLE_EVENT, null, values);
		}
		// TODO send update Notification
	}

	public void writeMatchesToDatabase(List<Match> matchList) {
		Log.d(TAG, "writeMatchesToDatabase");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		for (Match match : matchList) {
			Log.d(TAG, "Write " + match.toString() + " to database");
			values.clear();
			values.put(M_ID, match.getId());
			values.put(M_HOMECLUBID, match.getHomeClubId());
			values.put(M_AWAYCLUBID, match.getAwayClubId());
			values.put(M_DATE, match.getDate());
			values.put(M_TIMESTAMP, match.getTimestamp());
			db.replace(TABLE_MATCH, null, values);
		}
		// TODO send update Notification
	}

	public void writePlayersToDatabase(List<Player> playerList) {
		Log.d(TAG, "writeMatchesToDatabase");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		for (Player player : playerList) {
			Log.d(TAG, "Write " + player.toString() + " to database");
			values.clear();
			values.put(P_ID, player.getId());
			values.put(P_NAME, player.getName());
			values.put(P_TIMESTAMP, player.getTimestamp());
			// TODO Picture not handled yet
			db.replace(TABLE_PLAYER, null, values);
		}
		// TODO send update Notification
	}

	public void writePlaysForToDatabase(List<PlaysFor> playsForList) {
		Log.d(TAG, "writeMatchesToDatabase");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		for (PlaysFor playsFor : playsForList) {
			Log.d(TAG, "Write " + playsFor.toString() + " to database");
			values.clear();
			values.put(PF_ID, playsFor.getId());
			values.put(PF_CLUBID, playsFor.getClubId());
			values.put(PF_PLAYERID, playsFor.getPlayerId());
			values.put(PF_START, playsFor.getStart());
			values.put(PF_END, playsFor.getEnd());
			values.put(PF_TIMESTAMP, playsFor.getTimestamp());
			db.replace(TABLE_PLAYSFOR, null, values);
		}
		// TODO send update Notification
	}
}
